Supplementary Appendix: Agricultural Insurance Analysis of the Pacific Northwest, 2001 to 2015

Note: The material contained herein is supplementary to the article named in the title and published in the American Journal of Agricultural Economics (AJAE). This supplementary material contains the following items:

Supplemental Appendix Summary Statement

This supplemental appendix documents the exploratory data analysis process Here we explore the relationships of agricultural commodity loss, at a county level, from 2001-2015, across the three state region of the Pacific Northwest (Washington, Idaho, and Oregon), and then focus in on the 26 county region of the Inland Pacific Northwest (IPNW). We examine the entire range of commodities and damage causes for the IPNW study area, identifying the top revenue loss commodities and their most pertinent damage causes, as indicated from the USDA’s agricultural commodity loss insurance archive. We also explore claim freqency, and finally zero in on the top 5 commodities for the region, and examine specific loss and claim frequency by year and damage cause.

We perform several steps to examine the full set of data, and to narrow down factors to a usable form for further modeling analysis.

Step 1. Data Preparation: Loading and Transforming data. Here we load commodity loss data, and aggregate by differing factors, so we can examine losses on an annual basis, by county, damage cause, and commodity.

Step 2. Pacific Northwest (PNW) Overview. Here we examine overall commodity loss for the entire three state region of Washington, Idaho, and Oregon, which comes primarily from three primary agricultural regions - the IPNW (mostly Washington, with portions of northern Idaho and northeastern Oregon), the Southern Idaho Valley (Idaho), and the Willamette Valley (Oregon).

Step 3. PNW region overview of insurance loss by year, damage cause, and commodity, and county: 2001-2015

Step 4. PNW region overview of insurance Loss claim counts by year, damage cause, commodity, and county: 2001-2015

Step 5. Inland Pacific Northwest (IPNW) Overview. Given our review of insurance loss for the entire PNW - with a large proportion of loss occuring in the IPNW, were here focus on this region and more specifically examine loss and claim counts.

Step 6. IPNW region overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015

Step 7. IPNW region overview of Insurance Loss claim counts: 2001-2015

Step 8. WHEAT: IPNW region overview of Insurance Loss 2001-2015

Step 1: Data Preparation

The first step was to load Pacific Northwest agricultural insurance commodity loss data, aggregate by county, damage cause and commodity, remove zeros, transform the loss values ($) into cube root and logrithmic outputs, and scale and center the data.

Original data file for all insurance claims for the Pacific Northwest, can be found here:

https://github.com/erichseamon/paper-PHD1/blob/master/data/RMA_PNW_2001_2015.csv

Aggregated data file, which summarizes data by year, county, commodity, and damage cause, for just the Pacific Northwest, can be found here:

https://github.com/erichseamon/paper-PHD1/blob/master/data/RMA_damage_PNW_2001_2015.csv

All code that generates the following graphs and tables, can be found here:

https://github.com/erichseamon/paper-PHD1/

Step 1a: Original Insurance Loss Dataset - Pacific Northwest

The following table is an example of the original data that was acquired from the USDA Risk Management Agency (RMA). Each record represents an individual insurance claim.

	year	state	county	commodity	damagecause	monthcode	month	acres	loss	lossperacre	cropyear
11338	2001	ID	Ada	All Other Crops	Drought	9	SEP	17.000	153.00	9.000000	2001
11339	2001	ID	Ada	All Other Crops	Heat	8	AUG	105.200	5249.00	49.895437	2001
11340	2001	ID	Ada	All Other Crops	Freeze	4	APR	125.000	4500.00	36.000000	2001
11341	2001	ID	Ada	All Other Crops	Wind/Excess Wind	5	MAY	50.000	1800.00	36.000000	2001
11342	2001	ID	Ada	All Other Crops	Wind/Excess Wind	4	APR	92.500	3330.00	36.000000	2001
11343	2001	ID	Bannock	WHEAT	Drought	8	AUG	133.000	1212.00	9.112782	2001
11344	2001	ID	Bannock	WHEAT	Drought	9	SEP	777.520	24807.00	31.905289	2001
11345	2001	ID	Bannock	WHEAT	Drought	7	JUL	3529.754	54726.46	15.504327	2001
11346	2001	ID	Bannock	WHEAT	Heat	7	JUL	19.796	2371.60	119.801980	2001
11347	2001	ID	Bannock	WHEAT	Heat	8	AUG	25.000	904.00	36.160000	2001

Step 1b: Aggregated Insurance Loss Dataset - Pacific Northwest

The following table is an example of an aggregated dataset derived from the original files. Here we have summarized claims by year, county, commodity, and damage cause. Each unique combination is summarized, which includes the total summarized loss, the number of claims, the total summarized acreage, loss per acre, loss per claim, and acres per claim. This dataset was the basis for our data examination.

	year	state	county	commodity	damagecause	loss	count	acres	lossperacre	lossperclaim	acresperclaim
11	2014	ID	Ada	All Other Crops	Area Plan Crops Only	1398	1	0	0.0000000	1398.0	0
12	2015	ID	Ada	All Other Crops	Area Plan Crops Only	11810	1	0	0.0000000	11810.0	0
211	2008	OR	Baker	All Other Crops	Area Plan Crops Only	15292	2	5482	2.7894929	7646.0	2741
212	2010	OR	Baker	All Other Crops	Area Plan Crops Only	1819	2	2282	0.7971078	909.5	1141
215	2009	ID	Bannock	All Other Crops	Area Plan Crops Only	2284	1	600	3.8066667	2284.0	600
245	2008	ID	Bear Lake	All Other Crops	Area Plan Crops Only	8102	1	2468	3.2828201	8102.0	2468
246	2012	ID	Bear Lake	All Other Crops	Area Plan Crops Only	7459	1	2468	3.0222853	7459.0	2468
291	2010	ID	Bingham	All Other Crops	Area Plan Crops Only	4197	1	192	21.8593750	4197.0	192
292	2011	ID	Bingham	All Other Crops	Area Plan Crops Only	7769	1	240	32.3708333	7769.0	240
293	2012	ID	Bingham	All Other Crops	Area Plan Crops Only	6786	1	168	40.3928571	6786.0	168

Step 3: Pacific Northwest(PNW) Study Area

The following map shows the three key agricultural regions that make up over 95% of all agricultural insurance loss for the three state region of Washington, Oregon, and Idaho.

Step 4: PNW insurance claim loss summary

PNW total insurance loss by year, 2001-2015
2001-2015
damage cause	loss
Drought	$719,610,396
Decline in Price	$336,993,981
Heat	$267,376,106
Frost	$170,482,583
Freeze	$149,373,220
Excess Moisture/Precip/Rain	$148,556,853
Hail	$127,772,100
Cold Wet Weather	$86,741,055
Cold Winter	$67,118,523
Failure Irrig Supply	$57,087,916

PNW total insurance loss by year, 2001-2015
2001-2015
commodity	loss
WHEAT	$1,444,149,995
CHERRIES	$156,670,513
APPLES	$156,063,380
POTATOES	$117,459,400
BARLEY	$102,125,046
DRY PEAS	$58,038,162

PNW total insurance loss by year, 2001-2015
2001-2015
year	loss
2001	$66,499,267
2002	$96,367,644
2003	$103,553,264
2004	$52,006,242
2005	$72,871,250
2006	$63,467,130
2007	$83,252,301
2008	$150,480,619
2009	$545,650,029
2010	$83,829,994
2011	$115,965,036
2012	$89,684,692
2013	$171,121,810
2014	$222,526,098
2015	$296,416,750

Step 4: PNW insurance claim freqency summary

Step 5: IPNW Study Area

Step 5: Principle Components Analysis (PCA)

In Step 5, we use principle components analysis to examine the variation of damage causes and commodities, across counties and years.

Step 6: IPNW insurance claim loss summary

Step 7: IPNW insurance claim loss summary

Error: First argument, `data`, must be a data frame or shared data.

STEP 8: APPLES, 2001-2015 for the IPNW

STEP 9: BARLEY, 2001-2015 for the IPNW

Error: attempt to use zero-length variable name

STEP 10: WHEAT, 2001-2015 for the IPNW

STEP 11: CHERRIES, 2001-2015 for the IPNW

STEP 12: DRY PEAS, 2001-2015 for the IPNW